const { db, query } = require('../database/db')
class CostModel {
    // 学生缴费记录添加
    static async add(date, student_id, cost) {
        // 1.先连表查询费用-此学生应交费用
        let edSys_costSql = `
            SELECT edSys_cost.cost as should_cost,students.id,students.name
            FROM students
            JOIN edSys_cost ON students.ed_sys = edSys_cost.edSys
            WHERE students.id = ?
        `
        // 2.执行添加学生缴费记录
        let addCostSql;
        // 3.执行查询语句看，学生缴纳此次记录后的钱是否交完
        let sumSql = `
            SELECT SUM(cost) as costed,student_cost.student_id,students.name as student_name
            FROM student_cost
            JOIN students ON student_cost.student_id = students.id
            WHERE student_cost.student_id = ?
            GROUP BY student_id
        `
        let edSys_costResult = await query(edSys_costSql, [student_id])
        console.log('edSys_costResult::',edSys_costResult)
        let sumResult = await query(sumSql, [student_id])
        console.log('sumResult',sumResult)
        // 剩余费用的计算
        let remain_cost = Number(edSys_costResult[0].should_cost) - Number(sumResult[0].costed) - Number(cost)
        console.log('remain_cost::',remain_cost)
        //  注意：：： remain_cost == 0 
        if (remain_cost == 0) {
            addCostSql = `INSERT INTO student_cost (date,student_id,cost,state,remain_cost) VALUES (?,?,?,?,?)`
            return query(addCostSql, [date, student_id, cost, '已交完', 0])
        } else {
            addCostSql = `INSERT INTO student_cost (date,student_id,cost,state,remain_cost) VALUES (?,?,?,?,?)`
            return query(addCostSql, [date, student_id, cost, '未交完', remain_cost])
        }
    }

    // 搜索与分页：姓名、手机号、学制、专业
    static async list(student_name, student_phone, ed_sys, student_program, page_size, page) {
        let sql = `
            SELECT student_cost.date,student_cost.student_id,student_cost.cost,student_cost.state,
                student_cost.remain_cost,
                students.name as student_name,students.phone as student_phone,students.ed_sys as ed_sys,
                programs.name as student_program
            FROM student_cost
            JOIN students ON student_cost.student_id = students.id
            JOIN classes ON students.class_id = classes.id
            JOIN programs ON classes.program_id = programs.id
        `
        // 初始化参数数组
        let params = [];

        // 如果有搜索条件，添加到SQL语句中
        let conditions = [`student_cost.state_del = 0`]; // 初始化条件数组
        // 根据前端的请求数据加条件
        if (student_name) {
            conditions.push(`students.name = ?`);
            params.push(student_name);
        }
        if (student_phone) {
            conditions.push(`students.phone like ?`);
            params.push(student_phone);
        }
        if (ed_sys) {
            conditions.push(`students.ed_sys = ?`);
            params.push(ed_sys);
        }
        if (student_program) {
            conditions.push(`programs.id = ?`);
            params.push(student_program);
        }
        // 如果有搜索条件，添加WHERE子句
        if (conditions.length > 1) {
            sql += ` WHERE ${conditions.join(' AND ')}`;
        }

        // 添加分页的SQL语句
        sql += ` LIMIT ? OFFSET ?`;
        // 添加分页参数
        params.push(page_size);
        params.push((page - 1) * page_size);
        // 计算总数的SQL语句
        let countSql = `
        SELECT COUNT(*) as count FROM student_cost 
        JOIN students ON student_cost.student_id = students.id
        JOIN classes ON students.class_id = classes.id
        JOIN programs ON classes.program_id = programs.id
        WHERE ${conditions.join(' AND ')}`;
        // 执行查询
        const [costResult, countResult] = await Promise.all([
            query(sql, params), // 查询作业列表
            query(countSql, params) // 查询总数
        ]);
        return {
            cost: costResult, // 作业列表
            total: countResult // 总数
        };
    }

    // 学生缴费记录删除-软删除
    static del_f(id) {
        let sql = `
            UPDATE student_cost SET state_del = 1 WHERE id = ?
        `
        return query(sql, [id])
    }
    // 学生缴费记录删除-真删除
    static del_t(id) {
        let sql = `
            DELETE FROM student_cost WHERE id = ?
        `
        return query(sql, [id])
    }
    // 学生缴费记录的获取数据
    static get(id) {
        let sql = `
            SELECT student_cost.date,student_cost.student_id,student_cost.cost,student_cost.state,
                student_cost.remain_cost,
                students.name as student_name,students.phone as student_phone,students.ed_sys as ed_sys,
                programs.name as student_program
            FROM student_cost
            JOIN students ON student_cost.student_id = students.id
            JOIN classes ON students.class_id = classes.id
            JOIN programs ON classes.program_id = programs.id
            WHERE student_cost.id = ? AND student_cost.state_del = 0
        `
        return query(sql, [id])
    }
    // 学生缴费记录的设置数据
    static async set(id, date, cost) {
        // 通过学生缴费记录知道学生id
        let student_idSql = `
        SELECT student_id FROM student_cost WHERE id = ?
        `
        let studentResult = await query(student_idSql,[id])
        // 1.先连表查询费用-此学生应交费用
        let edSys_costSql = `
            SELECT edSys_cost.cost as should_cost,students.id,students.name
            FROM students
            JOIN edSys_cost ON students.ed_sys = edSys_cost.edSys
            WHERE students.id = ?
        `
        // 2.执行添加学生缴费记录
        let setCostSql;
        // 3.执行查询语句看，学生缴纳此次记录后的钱是否交完
        let sumSql = `
            SELECT SUM(cost) as costed,student_cost.student_id,students.name as student_name
            FROM student_cost
            JOIN students ON student_cost.student_id = students.id
            WHERE student_cost.student_id = ?
            GROUP BY student_id
        `
        let edSys_costResult = await query(edSys_costSql, [studentResult[0].student_id])
        let sumResult = await query(sumSql, [studentResult[0].student_id])
        // 剩余费用的计算
        let remain_cost = Number(edSys_costResult[0].should_cost) - Number(sumResult[0].costed) - Number(cost)
        if (edSys_costResult[0].should_cost = sumResult[0].costed) {
            setCostSql = `UPDATE student_cost SET date=?,cost=?,state=?,remain_cost=? WHERE id = ?`
            return query(setCostSql, [date, cost, '已交完', 0, id])
        } else {
            setCostSql = `INSERT INTO student_cost (date,student_id,cost,state,remain_cost) VALUES (?,?,?,?,?)`
            return query(setCostSql, [date, cost, '未交完', remain_cost, id])
        }
    }
}
module.exports = CostModel